﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.Common;

namespace DAL
{
    public class ProductDAL : AbstractDAL<Product>
    {
        static readonly string INSERT = "insert into Product values(null,@Name,@Price,@Summary,@Detail,@IndexImage);select last_insert_rowid() newid;";
        static readonly string UPDATE = "update Product set Name=@Name,Price=@Price,Summary=@Summary,Detail=@Detail,IndexImage=@IndexImage where id=@ID";
        static readonly string DELETE = "delete from Product where id=@ID";
        static readonly string SELECT = "select * from  Product where id=@ID";
        static readonly string SELECT_ALL = "select * from  Product";
        static readonly string SELECT_ALL_BY_CID = "select * from product where id in(select distinct pid from productcategory where cid in ({0}))";
        static readonly string SELECT_ALL_BY_KID = "select * from product where id in(select distinct pid from productkey where kid in ({0}))";
        static readonly string SELECT_ALL_BY_NAME = "select * from  Product where Name like @Name";

        protected override string InsertCommand
        {
            get
            {
                return INSERT;
            }
        }

        protected override string UpdateCommand
        {
            get { return UPDATE; }
        }

        protected override string DeleteCommand
        {
            get { return DELETE; }
        }

        protected override string SelectCommand
        {
            get { return SELECT; }
        }

        protected override string SelectAllCommand
        {
            get { return SELECT_ALL; }
        }

        protected override DbParameter[] GetInsertParameter(Product model)
        {
            return new DbParameter[] { 
                SqlHelper.CreateParameter("@Name", model.Name) ,
                SqlHelper.CreateParameter("@Price", model.Price) ,
                SqlHelper.CreateParameter("@Summary", model.Summary) ,
                SqlHelper.CreateParameter("@Detail", model.Detail) ,
                SqlHelper.CreateParameter("@IndexImage", model.IndexImage) 
            };
        }

        protected override void SyncID(Product model, object scalar)
        {
            model.ID = (long)scalar;
        }

        protected override DbParameter[] GetUpdateParameter(Product model)
        {
            return new DbParameter[] { 
                SqlHelper.CreateParameter("@ID", model.ID) ,
                SqlHelper.CreateParameter("@Name", model.Name) ,
                SqlHelper.CreateParameter("@Price", model.Price) ,
                SqlHelper.CreateParameter("@Summary", model.Summary) ,
                SqlHelper.CreateParameter("@Detail", model.Detail) ,
                SqlHelper.CreateParameter("@IndexImage", model.IndexImage) 
            };
        }

        protected override DbParameter[] GetDeleteParameter(Product model)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@ID", model.ID) };
        }

        protected override DbParameter[] GetSeleteParameter(long id)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@ID", id) };
        }

        protected override Product GetModel(DbDataReader reader)
        {
            Product entity = new Product();
            entity.ID = (long)reader["ID"];
            entity.Name = (string)reader["Name"];
            entity.Price = (decimal)reader["Price"];

            if (!(reader["Summary"] is DBNull))
            {
                entity.Summary = (string)reader["Summary"];
            }
            if (!(reader["Detail"] is DBNull))
            {
                entity.Detail = (string)reader["Detail"];
            }
            if (!(reader["IndexImage"] is DBNull))
            {
                entity.IndexImage = (string)reader["IndexImage"];
            }
            return entity;
        }

        public List<Product> GetAllByCID(IList<long> cidList)
        {
            List<Product> list = new List<Product>();
            if (cidList.Count == 0) { return list; }

            DbDataReader reader = null;
            string sql = null;
            sql = string.Format(SELECT_ALL_BY_CID, string.Join(",", cidList));
            try
            {
                reader = SqlHelper.GetCommand(sql).ExecuteReader();
                while (reader.Read())
                {
                    list.Add(GetModel(reader));
                }
            }
            finally
            {
                reader.Close();
            }
            return list;
        }

        public List<Product> GetAllByKID(IList<long> kidList)
        {
            List<Product> list = new List<Product>();
            if (kidList.Count == 0) { return list; }

            DbDataReader reader = null;
            string sql = null;
            sql = string.Format(SELECT_ALL_BY_KID, string.Join(",", kidList));
            try
            {
                reader = SqlHelper.GetCommand(sql).ExecuteReader();
                while (reader.Read())
                {
                    list.Add(GetModel(reader));
                }
            }
            finally
            {
                reader.Close();
            }
            return list;
        }

        public List<Product> GetAllByName(string keyword, List<long> categoryFilterID = null, List<long> keyFilterID = null)
        {
            List<Product> list = new List<Product>();

            DbDataReader reader = null;
            DbCommand command = null;
            string sql = SELECT_ALL_BY_NAME;

            if (categoryFilterID != null && categoryFilterID.Count != 0)
            {
                sql += string.Format(" and id in(select distinct pid from productcategory where cid in ({0})) ", string.Join(",", categoryFilterID));
            }

            if (keyFilterID != null && keyFilterID.Count != 0)
            {
                sql += string.Format(" and id in(select distinct pid from productkey where kid in ({0})) ", string.Join(",", keyFilterID));
            }

            try
            {
                command = SqlHelper.GetCommand(sql);
                command.Parameters.Add(SqlHelper.CreateParameter("@Name", "%" + keyword + "%"));
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(GetModel(reader));
                }
            }
            finally
            {
                SqlHelper.Close(command);
            }
            return list;
        }
    }
}
